/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import lx_easyfinace.util.CalculateUtil;
import model.Color;
import model.Constants;
import model.Guest;
import model.Product;
import model.Sell;
import model.SellItem;
import model.TableInterface;
import model.Vendor;

/**
 *
 * @author Administrator
 */
public class DatabaseManager {

    /**
     * Oracle数据库连接
     */
    public static Connection oracleConnect = new OracleConnect().getConnection();

    public static void main(String args[]) throws SQLException {
//        PreparedStatement pstm = oracleConnect.prepareStatement("delete from sell where sid=1");
//        pstm.execute();
        

        //        System.out.println(new Date());
        //        try {
        //            oracleConnect.setAutoCommit(false);
        //            new DatabaseManager().insertSellRecord();
        //            oracleConnect.commit();
        //            oracleConnect.setAutoCommit(true);
        //        } catch (SQLException ex) {
        //            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        //            if (oracleConnect != null) {
        //                try {
        //                    oracleConnect.rollback();
        //                    oracleConnect.setAutoCommit(true);
        //                } catch (SQLException ex1) {
        //                    Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex1);
        //                }
        //            }
        //        }
//        try {
//            List<String> test = new DatabaseManager().queryData("廖川南");
//
//            List<Guest> customerList = new DatabaseManager().querGuest();
//
//            for (Guest s : customerList) {
//                System.out.println(s);
//            }
//
//        } catch (SQLException ex) {
//            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
//        }

//        DatabaseManager dm = new DatabaseManager();
//        try {
//            dm.insertSellRecord();
//        } catch (SQLException ex) {
//            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
//        }

    }

    /**
     *插据库--销售记录
     * ----用于测试而已
     * 实际应用在DataOperateUtil中调用插入方法
     */
    private void insertSellRecord() throws SQLException {

        /* 查询是否可以找到此人. */
        String guestName = "admin727";

        Guest guest = new Guest();

        guest = (Guest) getObj(guest, "gname", guestName);

        if (!guest.isExist()) {
            guest.setgId(getIDNums(guest));
            guest.setgAddress("china");
            guest.setgName(guestName);
            guest.setgPhone("12345678900");
            System.out.println("这是一个新客户");
        } else {
            System.out.println("这是一个老客户");
        }

        /* 找到将此人信息取出,找不到.将其插入数据库 */
//        if (resultSet.next()) {
//            guest.setgId(resultSet.getInt("GID"));
//            guest.setgName(resultSet.getString("GNAME"));
//            guest.setgAddress(resultSet.getString("GADDRESS"));
//            guest.setgPhone(resultSet.getString("GPHONE"));
//            guest.setExist(true);
//        } else {
//            guest.setgId(getIDNums("guest", "gid"));
//            guest.setgAddress("china");
//            guest.setgName(guestName);
//            guest.setgPhone("12345678900");
//        }

        /*          ----这是一次售货记录----
         * 亚麻布:2匹,一匹是红色57米, 一匹是黑色80米..单价的15元/米.
         * 丝绸:3匹,一匹的白色 80米,一匹的灰色40米,一匹是红色50米..单价20元/米.
         */
        Sell sell = new Sell();
        sell.setsId(getIDNums(sell));
        sell.setsStutus(Constants.SELL_STATUS_PAYED);
        sell.setsDate(new Date());
        //(57+80)*15 + (80+40+50)*20 = XXX,这里没有算，随便写的个价格888
        sell.setsTotalPrice(888);
        sell.setsGuest(guest);

        //厂家的信息，暂时没有做
        Vendor v = new Vendor();
        v.setvId(-1);

        Product product = new Product();
        String pName = "亚麻布";
        product = (Product) getObj(product, "pname", pName);

        if (!product.isExist()) {
            product.setpId(getIDNums(product));
            product.setpName(pName);
            product.setpPrice(66);
            product.setpStorage(Constants.STORAGE_UNKNOW);
            product.setVendor(v);
            System.out.println("这是一种新产品");
        } else {
            System.out.println("这是一种老产品");
        }

        //颜色信息
        Color color = new Color();
        //根据品名和色号，去找相应的颜色ID。这里是亚麻布
        int cid = this.getProductColorID("004", product.getpId());
//        String colorNum = "004";
        color = (Color) getObj(color, "cid", String.valueOf(cid));

        if (!color.isExist()) {
            color.setCid(getIDNums(color));
            color.setPid(product.getpId());
            color.setColornum("004");
            color.setColordesc("红色");
        }

        SellItem sellItem1 = new SellItem();
        int sellItemID = getIDNums(sellItem1);
        sellItem1.setSiId(sellItemID++);
        sellItem1.setSiSell(sell);
        sellItem1.setSiProduct(product);
        sellItem1.setSiColor(color);
        sellItem1.setSiProductNum(57); /*销售产品的米数,可能多种产品,因此需要更多的SellItem */  //004 红色57米 亚麻布
        sellItem1.setSiperprice(15); /* 单价,可能会不同，不同的人可能有不同的价格*/
        sellItem1.setSiSubTotal(sellItem1.getSiProductNum() * sellItem1.getSiperprice());


        SellItem sellItem2 = new SellItem();
        sellItem2.setSiId(sellItemID++);
        sellItem2.setSiSell(sell);
        sellItem2.setSiProduct(product);
        sellItem2.setSiProductNum(80);//黑色80米 亚麻布
        sellItem2.setSiperprice(15);
        sellItem2.setSiSubTotal(sellItem2.getSiProductNum() * sellItem2.getSiperprice());

        SellItem sellItem3 = new SellItem();
        sellItem3.setSiId(sellItemID++);
        sellItem3.setSiSell(sell);
        sellItem3.setSiProduct(product);
        sellItem3.setSiProductNum(80);//白色80米 丝绸
        sellItem3.setSiperprice(20);
        sellItem3.setSiSubTotal(sellItem3.getSiProductNum() * sellItem3.getSiperprice());

        SellItem sellItem4 = new SellItem();
        sellItem4.setSiId(sellItemID++);
        sellItem4.setSiSell(sell);
        sellItem4.setSiProduct(product);
        sellItem4.setSiProductNum(40);// 灰色40米 丝绸
        sellItem4.setSiperprice(20);
        sellItem4.setSiSubTotal(sellItem4.getSiProductNum() * sellItem4.getSiperprice());

        SellItem sellItem5 = new SellItem();
        sellItem5.setSiId(sellItemID++);
        sellItem5.setSiSell(sell);
        sellItem5.setSiProduct(product);
        sellItem5.setSiProductNum(50);// 红色50米 丝绸
        sellItem5.setSiperprice(20);
        sellItem5.setSiSubTotal(sellItem4.getSiProductNum() * sellItem4.getSiperprice());




        SellItem[] sellItems = new SellItem[]{
            sellItem1, sellItem2, sellItem3, sellItem4, sellItem5
        };
        try {
            sell(guest, sell, product, sellItems);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * 查找色号的ID
     */
    public int getProductColorID(String colorNum, int pid) throws SQLException {
        //根据品名和色号，去找相应的颜色ID。这里是XX布
        String querySql = "SELECT cid FROM color WHERE pid = " + pid + " AND " + "colornum = " + "'" + colorNum + "'";
        ResultSet rs = getResultSet(querySql);
        if (rs.next()) {
            return rs.getInt("cid");
        } else {
            return -2;
        }

    }

    /**
     * 查询简单结果集
     * @param query 查询字段
     * @param tableName 查询表明
     * @return 指定条件的结果集
     * @throws SQLException 
     */
    public List<String> query(String query, String tableName) throws SQLException {
        List<String> resultList = new ArrayList<String>();
        String querySql = "SELECT " + query + " FROM " + tableName;
        ResultSet rs = getResultSet(querySql);
        while (rs.next()) {
            resultList.add(rs.getString(1));
        }

        return resultList;
    }

    /**
     * 获取 现有 客户 列表
     * @param name
     * @return
     * @throws SQLException
     */
    public List<Guest> querGuest() throws SQLException {
        List<Guest> resultList = new ArrayList<Guest>();

        String querySql = "SELECT gname, gphone, gaddress FROM GUEST";
        ResultSet resultSet = getResultSet(querySql);

        while (resultSet.next()) {
            Guest guest = new Guest();
            guest.setgName(resultSet.getString(1));
            guest.setgPhone(resultSet.getString(2));
            guest.setgAddress(resultSet.getString(3));

            resultList.add(guest);
        }

        return resultList;
    }

    /**
     * 查询已有的数据
     * @param args 查询条件(姓名)    //未考虑重名问题,暂时.
     * @throws SQLException
     * 例如:
     * getObject tg.gname 姓名, ts.sdate 日期, tp.pname 品名, ts.ssellquantity 数量, tp.pprice 价格, ts.stotalprice 金额, ts.spayed 已付款#退货, ts.stotalprice-ts.spayed 未付款
     * from guest tg
     * join sell ts on tg.gid = ts.gid
     * join product tp on ts.pid = tp.pid
     * where tg.gname = '廖川南'
     */
    public List<String> queryData(String name) throws SQLException {
//        String querySql = "SELECT tg.gname, ts.sdate, tp.pname, ts.ssellquantity, tp.pprice, ts.stotalprice, ts.spayed, ts.stotalprice-ts.spayed  FROM guest tg "
//                + "join sell ts on tg.gid=ts.gid"
//                + " "
//                + "join product tp on ts.pid=tp.pid"
//                + " "
//                + "WHERE tg.gname=" + "'" + name + "'";
        String querySql = "SELECT tg.gname, ts.sdate, ts.pname, ts.quantity, ts.price, ts.stotalprice, ts.payed, ts.stotalprice-ts.payed, ts.sid"
                + " FROM sell ts join guest tg on tg.gid=ts.gid "
                + "WHERE tg.gname=" + "'" + name + "'"
                + " order by sid";
        System.out.println(querySql);
//        PreparedStatement pstmt = null;
//        ResultSet myResultSet = null;
//        pstmt = oracleConnect.prepareStatement(querySql);
//        myResultSet = pstmt.executeQuery();
        ResultSet myResultSet = getResultSet(querySql);

        List<String> resultList = new ArrayList<String>();


        for (int i = 0; myResultSet.next(); i++) {
            String temp = null;
            String testName = myResultSet.getString(1);
            temp = CalculateUtil.calculateFormatDate(myResultSet.getDate(2))
                    + ", "
                    + myResultSet.getString(3)
                    + ", "
                    + myResultSet.getString(4)
                    + ", "
                    + myResultSet.getDouble(5)
                    + ", "
                    + myResultSet.getDouble(6)
                    + ", "
                    + myResultSet.getDouble(7)
                    + ", "
                    + (myResultSet.getDouble(6) - myResultSet.getDouble(7))
                    + ", "
                    + myResultSet.getInt(9)
                    ;

            resultList.add(temp);
        }

        return resultList;
    }

    /**
     * 插入卖货记录
     * @param guest 客户
     * @param sell 一次销售 
     * @param sellitems 此次销售明细
     * @return
     */
    public int sell(Guest guest, Sell sell, Product product, SellItem[] sellItems) throws SQLException {

        if (!guest.isExist()) {
            insertData(guest);
        }

        if (!product.isExist()) {
            insertData(product);
        }

//        insertData(sell);

        for (SellItem si : sellItems) {
            if (si.getSiColor() != null) {
                si.getSiColor().getCid();
                if (!si.getSiColor().isExist()) {
//                    insertData(si.getSiColor());
                }
            }
            insertData(si);
        }
        return -1;
    }

    /**
     * 设置表格唯一ID值.
     * 自增ID...(还有其他方法没有...?)
     * 查找需要增加记录的表中的最大ID值，然后在此基础上增加一个单位
     * @param table 表名
     * @param id 表的主键
     * @return 下一个正确的ID号
     * 关于异常:在这里截取异常有问题。。回导致无法让程序终止运行。
     * 因此将异常抛出，让上一级截取。
     */
    public int getIDNums(TableInterface table) throws SQLException {
        String sql = "SELECT max(" + table.getPrimaryKey() + ") FROM " + table.getTableName();
//        System.out.println(sql);
        ResultSet myResultSet = null;

        int idNums = -1;

//            pstmt = oracleConnect.prepareStatement(sql);
//            myResultSet = pstmt.executeQuery();
//            myResultSet.next();
//            System.out.println(myResultSet.getInt(1));
        myResultSet = this.getResultSet(sql);
        myResultSet.next();
        idNums = myResultSet.getInt(1);


        return idNums + 1;

    }

    /**
     *通用的实体类对象查找
     * @param table 表名
     * @param queryKey 以那个字段为关键字查询
     * @param queryCondition 查询条件
     *    例如: SELECT * FROM tableName WHERE queryKey = queryCondition;
     * @return 返回一个实例(根据表和查询条件)
     * @throws SQLException
     */
    public Object getObj(TableInterface table, String queryKey, String queryCondition) {
        String sql = "SELECT * FROM " + table.getTableName() + " WHERE " + queryKey + " = " + "'" + queryCondition + "'";
//        String sql = "SELECT * FROM " + table.getTableName();
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            resultSet = this.getResultSet(sql);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            return table.getObject(resultSet);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

//    public Object compareObj(Compareable table, String queryKey, String queryCondition) {
//
//    }
    /**
     * 通用实体类插入数据库接口
     * @param table 实现了TableInterface接口的实体类
     * @return
     */
    public void insertData(TableInterface table) throws SQLException {
        PreparedStatement pstmt = null;
        String tableName = table.getTableName();
        int argsSize = table.getTableColumnCount();
        String sql = "INSERT INTO " + tableName + " values " + "(" + CalculateUtil.calculateArgsSize(argsSize) + ")";

        System.out.println("sql:" + sql);


        pstmt = oracleConnect.prepareStatement(sql);
        table.insert(pstmt);
    }

    /**
     * 根据 SQL 语句 获取 结果集
     * @param querySql
     * @return ResultSet
     * @throws java.sql.SQLException
     */
    public ResultSet getResultSet(String querySql) throws java.sql.SQLException {
        PreparedStatement pstmt = null;
        ResultSet myResultSet = null;
        pstmt = oracleConnect.prepareStatement(querySql);
        myResultSet = pstmt.executeQuery();
        return myResultSet;
    }
}
